package com.supermap.dataservice.project.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 获取专家服务产品 决策服务产品 专业服务产品
 * @date 2018年10月26 下午 4:26
 * @email 1301584033@qq.com
 */
@Mapper
public interface PmsProductMapper {
    /**
     * 获取决策服务产品
     * */
    @Select("select t1.id, date_format(t1.create_date,'%Y-%m-%d %H:%i:%S')  as createDate,t1.folder_name,t1.product_mainpic,t1.product_title,t1.product_name from t_b_pms_product t1 \n" +
            "inner join \n" +
            "(select t2.id from  t_b_productgroup t2\n" +
            "inner join t_s_depart t3\n" +
            "on FIND_IN_SET(t3.ID,t2.production_unitid)\n" +
            "and (t3.depareacode = 50 or concat('50',right(t3.depareacode,2)) = #{areaCode})) tt2\n" +
            "on t1.product_group = tt2.id \n" +
            "and t1.product_state = 6\n" +
            "order by t1.create_date desc\n" +
            "limit #{pageSize},#{pageNum}")
    List<Map<String,Object>> getPsmProductByAreaCode(@Param("areaCode") int areaCode,@Param("pageSize") int pageSize,@Param("pageNum") int pageNum);

    /**
     * 获取决策服务产品
     * */
    @Select("select count(1) from t_b_pms_product t1 \n" +
            "inner join \n" +
            "(select t2.id from  t_b_productgroup t2\n" +
            "inner join t_s_depart t3\n" +
            "on FIND_IN_SET(t3.ID,t2.production_unitid)\n" +
            "and (t3.depareacode = 50 or concat('50',right(t3.depareacode,2)) = #{areaCode})) tt2\n" +
            "on t1.product_group = tt2.id \n" +
            "and t1.product_state = 6\n" +
            "order by t1.create_date desc\n" +
            "")
    int getPsmProductByAreaCodeCout(@Param("areaCode") int areaCode);



    /**
     * 获取各个区县统计产品接口
     *
     * */
    @SelectProvider(type = PmsSQl.class, method = "getAllProductContSumSql")
    List<Map<String, Object>> getAllProductContSum(@Param("beginTime") String beginTime, @Param("endTime") String endTime);


    /**
     * 根据产品大类和对应区域id查询
     * */
    @Select("select concat(left(tt2.depareacode,2),right(tt2.depareacode,2)) as depareacode,tt2.content_category as classId,\n" +
            "case  tt2.content_category\n" +
            "when 1 then '农业气象评价'\n" +
            "when 2 then '农用天气预报'\n" +
            "when 3 then '灾害监测与预警'\n" +
            "when 4 then '节气与农业生产'\n" +
            "when 5 then '农业气象实况'\n" +
            "when 6 then '农业科普与资讯'    \n" +
            "when 7 then '专题服务'\n" +
            "when 8 then '重要消息'\n" +
            "end as className   \n" +
            ", count(1) as countSum from t_b_pms_product t1 \n" +
            "inner join \n" +
            "(select t2.id,t3.depareacode,t2.content_category from  t_b_productgroup t2\n" +
            "inner join t_s_depart t3\n" +
            "on FIND_IN_SET(t3.ID,t2.production_unitid)\n" +
            "and ( concat('50',right(t3.depareacode,2))=#{areaCode})) tt2\n" +
            "on t1.product_group = tt2.id \n" +
            "group by  tt2.depareacode")
    List<Map<String,Object>> getDsByAreacode(@Param("areaCode") String  areaCode);



    /**
     * 根据产品大类id bigClassId和区域areaCode 获取产品数据
     * */
    @Select("select t1.product_title,tt2.group_name,  date_format(t1.make_date,'%Y-%m-%d %H:%i:%S') as make_date\n" +
            " from t_b_pms_product t1 \n" +
            "inner join \n" +
            "(select t2.id,t3.depareacode,t2.group_name,t2.content_category from  t_b_productgroup t2\n" +
            "inner join t_s_depart t3\n" +
            "on FIND_IN_SET(t3.ID,t2.production_unitid)\n" +
            "and (t3.depareacode = 50 or concat('50',right(t3.depareacode,2))=#{areaCode}) and t2.content_category=#{bigClassId}) tt2\n" +
            "on t1.product_group = tt2.id ")
    List<Map<String,Object>> getProductByclassIdAndAreaCode(@Param("bigClassId")String bigClassId,@Param("areaCode") String  areaCode);

 class PmsSQl{
     public String getAllProductContSumSql(Map<String, Object> map){
         String beginTime = (String)map.get("beginTime");
         String endTime = (String)map.get("endTime");
         StringBuffer stringBuffer=new StringBuffer();
         stringBuffer.append("  select a.depareacode depareacode,a.departname departname,d.count productCounSum,0 AS exeproductContSunm " +
                 ", 0 AS vipProductCountSum from ( " +
                 "select  t.id id,CONCAT(LEFT(depareacode,2),RIGHT(depareacode,2)) as depareacode,departname from t_s_depart t where  LENGTH(depareacode)> 4 " +
                 "UNION all " +
                 "select t.id id,depareacode,t.departname from t_s_depart t where  LENGTH(depareacode) = 2  " +
                 ") a left JOIN (SELECT " +
                 "b.production_unitid pid, " +
                 "count(c.id) count " +
                 "FROM " +
                 "t_b_productgroup b " +
                 "LEFT JOIN t_b_pms_product c ON c.product_group = b.id   "  );
         if(beginTime!=null&&endTime!=null){
             stringBuffer.append("   WHERE " +
                     "IF(c.make_date is  null,1=1," +
                     "DATE(c.make_date)  >= DATE("+beginTime+")" +
                     "AND DATE(c.make_date) <= DATE("+endTime+"))");
         }
         stringBuffer.append("  GROUP BY " +
                 "b.production_unitid) d " +
                 "on a.id=left(d.pid,LENGTH(d.pid)-1)");

         String str=stringBuffer.toString();
         System.out.println(str);
         return  stringBuffer.toString();

     }


 }

}

